﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections;
using System.Data;
using DTO;
using System.Xml;
using System.IO;

namespace DAO
{
    public class DataAccess
    {
        public static DataTable ImportDataXLS(String fileName, String table)
        {
            try
            {
                String connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
                String strSql = "select* from [" + table + "$]";
                OleDbConnection conn = new OleDbConnection(connString);
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        public static DataTable ImportDataXLSX(String fileName, String table)
        {
            try
            {
                String connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";";
                String strSql = "select* from [" + table + "$]";
                OleDbConnection conn = new OleDbConnection(connString);
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(strSql, conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw;
            }
        }
        
        private static OleDbConnection conn = null;
        public static bool DatabaseCheck()
        {
            if (File.Exists("GAS.mdb"))
            {
                return true;
            }
            return false;
        }
        public static String GetConnString()
        {
            try
            {
                String s;
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.Load("Config.xml");
                XmlNode rootNode = xmlDoc.DocumentElement;
                XmlNode connectionNode = rootNode.SelectSingleNode(@"//ConnectionString");
                s = connectionNode.InnerText;
                return s;
            }
            catch (Exception ex)
            {
                throw new Exception("Không thể mở CSDL");
            }
            //return "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=GAS.accdb;Jet OLEDB:Database Password=minhnhut;";
        }
        public static OleDbConnection OpenOleConnection()
        {
            try
            {
                if (conn == null)
                {
                    String connStr = GetConnString();
                    conn = new OleDbConnection(connStr);
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();                
                return conn;
            }
            catch (Exception ex) { return null; }
        }
        public static DataTable Query(String strQuery)
        {
            try
            {               
                OleDbConnection conn = OpenOleConnection();
                DataTable dt = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter(strQuery, conn);
                da.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public static DataTable Query(OleDbTransaction tran, String strQuery)
        {
            try
            {
                OleDbConnection conn = OpenOleConnection();
                DataTable dt = new DataTable();
                OleDbDataAdapter da = new OleDbDataAdapter(strQuery, conn);
                da.SelectCommand.Transaction = tran;
                da.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        } 
    }
    public class DecimalToInt
    {
        public static int Convert(Decimal argument)
        {
            object Int32Value;
            object UInt32Value;

            // Convert the argument to an int value.
            try
            {
                Int32Value = (int)argument;
                return (int)Int32Value;
            }
            catch (Exception ex)
            {

            }

            // Convert the argument to a uint value.
            try
            {
                UInt32Value = (uint)argument;
                return (int)UInt32Value;
            }
            catch (Exception ex)
            {

            }
            return 1;
        }
    }
}
